Shape Maps

The plugin currently supports US maps and state names. The model and data styles for the subsequent examples are:

explore: airports is table('malloy-data.faa.airports'){
  primary_key: code
  measure: airport_count is count(*)
  query: by_state is {
    where: state != null
    group_by: state
    aggregate: airport_count
  }
}

Data Styles

{
  "by_state": {
    "renderer": "shape_map"
  }
}

Run as a simple query

query: airports->{nest: by_state}

QUERY RESULTS

by_state

[
  {
    "by_state": [
      {
        "state": "TX",
        "airport_count": 1845
      },
      {
        "state": "CA",
        "airport_count": 984
      },
      {
        "state": "IL",
        "airport_count": 890
      },
      {
        "state": "FL",
        "airport_count": 856
      },
      {
        "state": "PA",
        "airport_count": 804
      },
      {
        "state": "OH",
        "airport_count": 749
      },
      {
        "state": "IN",
        "airport_count": 643
      },
      {
        "state": "AK",
        "airport_count": 608
      },
      {
        "state": "NY",
        "airport_count": 576
      },
      {
        "state": "WI",
        "airport_count": 543
      },
      {
        "state": "MO",
        "airport_count": 537
      },
      {
        "state": "MN",
        "airport_count": 507
      },
      {
        "state": "LA",
        "airport_count": 500
      },
      {
        "state": "MI",
        "airport_count": 489
      },
      {
        "state": "WA",
        "airport_count": 484
      },
      {
        "state": "OK",
        "airport_count": 443
      },
      {
        "state": "OR",
        "airport_count": 441
      },
      {
        "state": "GA",
        "airport_count": 440
      },
      {
        "state": "ND",
        "airport_count": 436
      },
      {
        "state": "CO",
        "airport_count": 425
      },
      {
        "state": "VA",
        "airport_count": 421
      },
      {
        "state": "KS",
        "airport_count": 415
      },
      {
        "state": "NC",
        "airport_count": 400
      },
      {
        "state": "NJ",
        "airport_count": 378
      },
      {
        "state": "AZ",
        "airport_count": 319
      },
      {
        "state": "IA",
        "airport_count": 319
      },
      {
        "state": "NE",
        "airport_count": 308
      },
      {
        "state": "AR",
        "airport_count": 299
      },
      {
        "state": "TN",
        "airport_count": 285
      },
      {
        "state": "AL",
        "airport_count": 260
      },
      {
        "state": "MT",
        "airport_count": 259
      },
      {
        "state": "MS",
        "airport_count": 243
      },
      {
        "state": "ID",
        "airport_count": 238
      },
      {
        "state": "MD",
        "airport_count": 229
      },
      {
        "state": "MA",
        "airport_count": 225
      },
      {
        "state": "KY",
        "airport_count": 202
      },
      {
        "state": "SC",
        "airport_count": 189
      },
      {
        "state": "NM",
        "airport_count": 181
      },
      {
        "state": "SD",
        "airport_count": 180
      },
      {
        "state": "ME",
        "airport_count": 164
      },
      {
        "state": "CT",
        "airport_count": 153
      },
      {
        "state": "UT",
        "airport_count": 140
      },
      {
        "state": "NV",
        "airport_count": 128
      },
      {
        "state": "WV",
        "airport_count": 116
      },
      {
        "state": "WY",
        "airport_count": 115
      },
      {
        "state": "NH",
        "airport_count": 112
      },
      {
        "state": "VT",
        "airport_count": 81
      },
      {
        "state": "HI",
        "airport_count": 52
      },
      {
        "state": "DE",
        "airport_count": 42
      },
      {
        "state": "PR",
        "airport_count": 40
      },
      {
        "state": "RI",
        "airport_count": 28
      },
      {
        "state": "DC",
        "airport_count": 20
      },
      {
        "state": "CQ",
        "airport_count": 11
      },
      {
        "state": "VI",
        "airport_count": 9
      },
      {
        "state": "AS",
        "airport_count": 4
      },
      {
        "state": "GU",
        "airport_count": 3
      },
      {
        "state": "WQ",
        "airport_count": 1
      },
      {
        "state": "MQ",
        "airport_count": 1
      }
    ]
  }
]

WITH __stage0 AS (
  SELECT
    group_set,
    CASE WHEN group_set=1 THEN airports.state END as state__1,
    CASE WHEN group_set=1 THEN COUNT( 1) END as airport_count__1
  FROM `malloy-data.faa.airports` as airports
  CROSS JOIN (SELECT row_number() OVER() -1  group_set FROM UNNEST(GENERATE_ARRAY(0,1,1)))
  WHERE (group_set NOT IN (1) OR (group_set IN (1) AND airports.state IS NOT NULL))
  GROUP BY 1,2
)
SELECT
  ARRAY_AGG(CASE WHEN group_set=1 THEN STRUCT(state__1 as state, airport_count__1 as airport_count) END IGNORE NULLS  ORDER BY  airport_count__1 desc ) as by_state
FROM __stage0

Run as a trellis

By calling the configured map as a nested subtable, a trellis is formed.

query: airports->{
  group_by: faa_region
  aggregate: airport_count
  nest: by_state
}

QUERY RESULTS

faa_region	airport_count	by_state
AGL	4,437
ASW	3,268
ASO	2,924
AEA	2,586
ANM	2,102

[
  {
    "faa_region": "AGL",
    "airport_count": 4437,
    "by_state": [
      {
        "state": "IL",
        "airport_count": 890
      },
      {
        "state": "OH",
        "airport_count": 749
      },
      {
        "state": "IN",
        "airport_count": 643
      },
      {
        "state": "WI",
        "airport_count": 543
      },
      {
        "state": "MN",
        "airport_count": 507
      },
      {
        "state": "MI",
        "airport_count": 489
      },
      {
        "state": "ND",
        "airport_count": 436
      },
      {
        "state": "SD",
        "airport_count": 180
      }
    ]
  },
  {
    "faa_region": "ASW",
    "airport_count": 3268,
    "by_state": [
      {
        "state": "TX",
        "airport_count": 1845
      },
      {
        "state": "LA",
        "airport_count": 500
      },
      {
        "state": "OK",
        "airport_count": 443
      },
      {
        "state": "AR",
        "airport_count": 299
      },
      {
        "state": "NM",
        "airport_count": 181
      }
    ]
  },
  {
    "faa_region": "ASO",
    "airport_count": 2924,
    "by_state": [
      {
        "state": "FL",
        "airport_count": 856
      },
      {
        "state": "GA",
        "airport_count": 440
      },
      {
        "state": "NC",
        "airport_count": 400
      },
      {
        "state": "TN",
        "airport_count": 285
      },
      {
        "state": "AL",
        "airport_count": 260
      },
      {
        "state": "MS",
        "airport_count": 243
      },
      {
        "state": "KY",
        "airport_count": 202
      },
      {
        "state": "SC",
        "airport_count": 189
      },
      {
        "state": "PR",
        "airport_count": 40
      },
      {
        "state": "VI",
        "airport_count": 9
      }
    ]
  },
  {
    "faa_region": "AEA",
    "airport_count": 2586,
    "by_state": [
      {
        "state": "PA",
        "airport_count": 804
      },
      {
        "state": "NY",
        "airport_count": 576
      },
      {
        "state": "VA",
        "airport_count": 421
      },
      {
        "state": "NJ",
        "airport_count": 378
      },
      {
        "state": "MD",
        "airport_count": 229
      },
      {
        "state": "WV",
        "airport_count": 116
      },
      {
        "state": "DE",
        "airport_count": 42
      },
      {
        "state": "DC",
        "airport_count": 20
      }
    ]
  },
  {
    "faa_region": "ANM",
    "airport_count": 2102,
    "by_state": [
      {
        "state": "WA",
        "airport_count": 484
      },
      {
        "state": "OR",
        "airport_count": 441
      },
      {
        "state": "CO",
        "airport_count": 425
      },
      {
        "state": "MT",
        "airport_count": 259
      },
      {
        "state": "ID",
        "airport_count": 238
      },
      {
        "state": "UT",
        "airport_count": 140
      },
      {
        "state": "WY",
        "airport_count": 115
      }
    ]
  }
]

WITH __stage0 AS (
  SELECT
    group_set,
    airports.faa_region as faa_region__0,
    CASE WHEN group_set=0 THEN COUNT( 1) END as airport_count__0,
    CASE WHEN group_set=1 THEN airports.state END as state__1,
    CASE WHEN group_set=1 THEN COUNT( 1) END as airport_count__1
  FROM `malloy-data.faa.airports` as airports
  CROSS JOIN (SELECT row_number() OVER() -1  group_set FROM UNNEST(GENERATE_ARRAY(0,1,1)))
  WHERE (group_set NOT IN (1) OR (group_set IN (1) AND airports.state IS NOT NULL))
  GROUP BY 1,2,4
)
SELECT
  faa_region__0 as faa_region,
  ANY_VALUE(CASE WHEN group_set=0 THEN airport_count__0 END) as airport_count,
  ARRAY_AGG(CASE WHEN group_set=1 THEN STRUCT(state__1 as state, airport_count__1 as airport_count) END IGNORE NULLS  ORDER BY  airport_count__1 desc ) as by_state
FROM __stage0
GROUP BY 1
ORDER BY 2 desc

Run as a trellis, repeated with different filters

query: airports->{
  group_by: faa_region
  aggregate: airport_count
  nest: [
    heliports is by_state {where: fac_type = 'HELIPORT'}
    seaplane_bases is by_state {where: fac_type = 'SEAPLANE BASE'}
  ]
}

QUERY RESULTS

faa_region	airport_count	heliports	seaplane_bases
AGL	4,437
ASW	3,268
ASO	2,924
AEA	2,586
ANM	2,102

[
  {
    "faa_region": "AGL",
    "airport_count": 4437,
    "heliports": [
      {
        "state": "IL",
        "airport_count": 245
      },
      {
        "state": "OH",
        "airport_count": 201
      },
      {
        "state": "IN",
        "airport_count": 115
      },
      {
        "state": "MI",
        "airport_count": 87
      },
      {
        "state": "WI",
        "airport_count": 85
      },
      {
        "state": "MN",
        "airport_count": 51
      },
      {
        "state": "SD",
        "airport_count": 26
      },
      {
        "state": "ND",
        "airport_count": 16
      }
    ],
    "seaplane_bases": [
      {
        "state": "MN",
        "airport_count": 72
      },
      {
        "state": "WI",
        "airport_count": 16
      },
      {
        "state": "IN",
        "airport_count": 12
      },
      {
        "state": "IL",
        "airport_count": 8
      },
      {
        "state": "MI",
        "airport_count": 7
      },
      {
        "state": "OH",
        "airport_count": 2
      },
      {
        "state": "ND",
        "airport_count": 1
      },
      {
        "state": "SD",
        "airport_count": 1
      }
    ]
  },
  {
    "faa_region": "ASW",
    "airport_count": 3268,
    "heliports": [
      {
        "state": "TX",
        "airport_count": 435
      },
      {
        "state": "LA",
        "airport_count": 229
      },
      {
        "state": "OK",
        "airport_count": 92
      },
      {
        "state": "AR",
        "airport_count": 80
      },
      {
        "state": "NM",
        "airport_count": 25
      }
    ],
    "seaplane_bases": [
      {
        "state": "LA",
        "airport_count": 17
      },
      {
        "state": "OK",
        "airport_count": 1
      },
      {
        "state": "NM",
        "airport_count": 1
      }
    ]
  },
  {
    "faa_region": "ASO",
    "airport_count": 2924,
    "heliports": [
      {
        "state": "FL",
        "airport_count": 280
      },
      {
        "state": "GA",
        "airport_count": 103
      },
      {
        "state": "TN",
        "airport_count": 87
      },
      {
        "state": "AL",
        "airport_count": 75
      },
      {
        "state": "NC",
        "airport_count": 70
      },
      {
        "state": "KY",
        "airport_count": 55
      },
      {
        "state": "MS",
        "airport_count": 49
      },
      {
        "state": "SC",
        "airport_count": 27
      },
      {
        "state": "PR",
        "airport_count": 20
      },
      {
        "state": "VI",
        "airport_count": 4
      }
    ],
    "seaplane_bases": [
      {
        "state": "FL",
        "airport_count": 43
      },
      {
        "state": "AL",
        "airport_count": 3
      },
      {
        "state": "VI",
        "airport_count": 3
      },
      {
        "state": "SC",
        "airport_count": 2
      },
      {
        "state": "TN",
        "airport_count": 2
      },
      {
        "state": "GA",
        "airport_count": 1
      },
      {
        "state": "KY",
        "airport_count": 1
      },
      {
        "state": "NC",
        "airport_count": 1
      },
      {
        "state": "PR",
        "airport_count": 1
      }
    ]
  },
  {
    "faa_region": "AEA",
    "airport_count": 2586,
    "heliports": [
      {
        "state": "PA",
        "airport_count": 307
      },
      {
        "state": "NJ",
        "airport_count": 247
      },
      {
        "state": "NY",
        "airport_count": 156
      },
      {
        "state": "VA",
        "airport_count": 126
      },
      {
        "state": "MD",
        "airport_count": 64
      },
      {
        "state": "WV",
        "airport_count": 33
      },
      {
        "state": "DC",
        "airport_count": 18
      },
      {
        "state": "DE",
        "airport_count": 13
      }
    ],
    "seaplane_bases": [
      {
        "state": "NY",
        "airport_count": 23
      },
      {
        "state": "WV",
        "airport_count": 10
      },
      {
        "state": "NJ",
        "airport_count": 10
      },
      {
        "state": "PA",
        "airport_count": 10
      },
      {
        "state": "VA",
        "airport_count": 4
      },
      {
        "state": "MD",
        "airport_count": 3
      },
      {
        "state": "DE",
        "airport_count": 1
      }
    ]
  },
  {
    "faa_region": "ANM",
    "airport_count": 2102,
    "heliports": [
      {
        "state": "CO",
        "airport_count": 165
      },
      {
        "state": "WA",
        "airport_count": 130
      },
      {
        "state": "OR",
        "airport_count": 100
      },
      {
        "state": "UT",
        "airport_count": 43
      },
      {
        "state": "ID",
        "airport_count": 36
      },
      {
        "state": "MT",
        "airport_count": 29
      },
      {
        "state": "WY",
        "airport_count": 24
      }
    ],
    "seaplane_bases": [
      {
        "state": "WA",
        "airport_count": 15
      },
      {
        "state": "ID",
        "airport_count": 5
      },
      {
        "state": "OR",
        "airport_count": 3
      },
      {
        "state": "MT",
        "airport_count": 2
      }
    ]
  }
]

WITH __stage0 AS (
  SELECT
    group_set,
    airports.faa_region as faa_region__0,
    CASE WHEN group_set=0 THEN COUNT( 1) END as airport_count__0,
    CASE WHEN group_set=1 THEN airports.state END as state__1,
    CASE WHEN group_set=1 THEN COUNT( 1) END as airport_count__1,
    CASE WHEN group_set=2 THEN airports.state END as state__2,
    CASE WHEN group_set=2 THEN COUNT( 1) END as airport_count__2
  FROM `malloy-data.faa.airports` as airports
  CROSS JOIN (SELECT row_number() OVER() -1  group_set FROM UNNEST(GENERATE_ARRAY(0,2,1)))
  WHERE ((group_set NOT IN (1) OR (group_set IN (1) AND (airports.state IS NOT NULL)
  AND (airports.fac_type='HELIPORT'))))
  AND ((group_set NOT IN (2) OR (group_set IN (2) AND (airports.state IS NOT NULL)
  AND (airports.fac_type='SEAPLANE BASE'))))
  GROUP BY 1,2,4,6
)
SELECT
  faa_region__0 as faa_region,
  ANY_VALUE(CASE WHEN group_set=0 THEN airport_count__0 END) as airport_count,
  ARRAY_AGG(CASE WHEN group_set=1 THEN STRUCT(state__1 as state, airport_count__1 as airport_count) END IGNORE NULLS  ORDER BY  airport_count__1 desc ) as heliports,
  ARRAY_AGG(CASE WHEN group_set=2 THEN STRUCT(state__2 as state, airport_count__2 as airport_count) END IGNORE NULLS  ORDER BY  airport_count__2 desc ) as seaplane_bases
FROM __stage0
GROUP BY 1
ORDER BY 2 desc